package com.ctshk.rpc.ship.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.ship.dto.*;
import com.ctshk.rpc.ship.entity.ShipProduct;
import com.ctshk.rpc.ship.req.*;
import com.ctshk.rpc.ship.req.app.AppShipProductIsRecommendedReq;
import com.ctshk.rpc.ship.req.app.PeoplesPriceDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 船票产品 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2021-03-05
 */
public interface ShipProductMapper extends BaseMapper<ShipProduct> {
    /**
     * 获取船票列表
     *
     * @param shipProductPageReq
     * @param data
     * @return
     */
    @Select("<script>" + "select t1.id,t1.code,t1.shelves_status shelvesStatus,t1.dep_city_name as depCityName ,t1.arr_city_name as arrCityName,t1.company_type companyType,t1.voyage\n" +
            ",t1.cabin_class_name as cabinClassName,IFNULL(substring(t1.dep_time,12,5),t1.dep_time) depTime,t2.dep_place_name as depPlaceName\n" +
            ",t2.arr_place_name as arrPlaceName,t1.ship_type_name as shipTypeName,t1.product_source product_source,t1.supplier_name as supplierName,t1.applicable_crowd_name as applicableCrowdName,t1.charge_user_id as chargeUserId,t1.charge_user_name chargeUserName ,\n" +
            "t1.sell_status sellStatus,t1.pack_status as packStatus,t1.approval_status as approvalStatus\n" +
            "from t_ship_product t1 \n" +
            "left join t_ship_route t2 on t1.route_id = t2.id \n" +
            "LEFT JOIN t_main_data_ship_cabinclass t3 on (t1.cabin_class = t3.type or t1.cabin_class_str = t3.type) "+
            "WHERE t1.is_deleted = 0 " +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t1.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.code != null and req.code != ''\"> AND t1.code = #{req.code}</if> \n" +
            "<if test=\"req.voyage != null and req.voyage != ''\"> AND t1.voyage LIKE CONCAT('%',#{req.voyage},'%')</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t1.dep_city_name = #{req.depCityName}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t1.arr_city_name = #{req.arrCityName}</if> \n" +

            "<if test=\"req.depPlaceName != null and req.depPlaceId != ''\"> AND t2.dep_place_name like  CONCAT('%',#{req.depPlaceName },'%')</if> \n" +
            "<if test=\"req.arrPlaceName != null and req.arrPlaceId != ''\"> AND t2.arr_place_name like  CONCAT('%',#{req.arrPlaceName },'%')</if> \n" +

            "<if test=\"req.productSource != null and req.productSource != ''\"> AND t1.product_source = #{req.productSource}</if> \n" +

            " <if test='data != null and data.size > 0'>" +
            " AND (t1.charge_user_id IN \n" +
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> or t1.create_id IN \n"+
            " <foreach collection='data' item='userId' open='(' separator=',' close=')'>"+
            " #{userId}"+
            " </foreach> )\n"+
            " </if>" +

            "<if test=\"req.depTimeStart != null and req.depTimeEnd != null \"> AND substring(t1.dep_time,12,5) between #{req.depTimeStart} and #{req.depTimeEnd} </if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND (t1.cabin_class = #{req.cabinClass} or t1.cabin_class_str = #{req.cabinClass}) </if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> AND t1.supplier_name LIKE CONCAT('%',#{req.supplierName },'%')</if> \n" +
            "order BY t1.gmt_create desc " + "</script>")
    IPage<ShipProductDTO> queryList(@Param("req") ShipProductPageReq shipProductPageReq, IPage<ShipProductDTO> agentPage, @Param("data") List<Long> data);


    /**
     * 获取船票列表
     *
     * @param shipProductPageReq
     * @return
     */
    @Select("<script>" + "select t1.id,t1.code,t1.dep_city_name as depCityName ,t1.arr_city_name as arrCityName,t1.company_type companyType,t1.voyage\n" +
            ",t3.content as cabinClass,IFNULL(substring(t1.dep_time,12,5),t1.dep_time) depTime,t2.dep_place_name as depPlaceName\n" +
            ",t2.arr_place_name as arrPlaceName,t1.ship_type_name as shipTypeName,t1.product_source product_source,t1.supplier_name as supplierName,t1.applicable_crowd_name as applicableCrowdName,t1.charge_user_id as chargeUserId,t1.charge_user_name chargeUserName ,\n" +
            "t1.sell_status sellStatus,t1.pack_status as packStatus,t1.approval_status as approvalStatus\n" +
            "from t_ship_product t1 \n" +
            "left join t_ship_route t2 on t1.route_id = t2.id \n" +
            "LEFT JOIN t_main_data_ship_cabinclass t3 on (t1.cabin_class = t3.type or t1.cabin_class_str = t3.type) "+
            "WHERE t1.is_deleted = 0 " +
//            "and t1.approval_status=5" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t1.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.code != null and req.code != ''\"> AND t1.code = #{req.code}</if> \n" +
            "<if test=\"req.voyage != null and req.voyage != ''\"> AND t1.voyage LIKE CONCAT('%',#{req.voyage},'%')</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t1.dep_city_name = #{req.depCityName}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t1.arr_city_name = #{req.arrCityName}</if> \n" +

            "<if test=\"req.depPlaceName != null and req.depPlaceId != ''\"> AND t2.dep_place_name like  CONCAT('%',#{req.depPlaceName },'%')</if> \n" +
            "<if test=\"req.arrPlaceName != null and req.arrPlaceId != ''\"> AND t2.arr_place_name like  CONCAT('%',#{req.arrPlaceName },'%')</if> \n" +

            "<if test=\"req.productSource != null and req.productSource != ''\"> AND t1.product_source = #{req.productSource}</if> \n" +

            "<if test=\"req.depTimeStart != null and req.depTimeEnd != null \"> AND substring(t1.dep_time,12,5) between #{req.depTimeStart} and #{req.depTimeEnd} </if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND (t1.cabin_class = #{req.cabinClass} or t1.cabin_class_str = #{req.cabinClass}) </if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> AND t1.supplier_name LIKE CONCAT('%',#{req.supplierName },'%')</if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> AND t1.supplier_name LIKE CONCAT('%',#{req.supplierName },'%')</if> \n" +
            "<if test=\"req.productIds != null\"> AND t1.id in <foreach item='item' index='index' collection='req.productIds' open='(' separator=',' close=')'> #{item} </foreach></if> \n" +
            "order BY t1.gmt_create desc " + "</script>")
    IPage<ShipProductDTO> queryShipList(@Param("req") ShipProductPageReq shipProductPageReq, IPage<ShipProductDTO> agentPage);


    /**
     * 获取船票列表
     *
     * @param shipProductPageReq
     * @return
     */
    @Select("<script>" + "select t1.id,t1.code,t1.dep_city_name as depCityName ,t1.arr_city_name as arrCityName,t1.company_type companyType,t1.voyage\n" +
            ",t3.content as cabinClass,IFNULL(substring(t1.dep_time,12,5),t1.dep_time) depTime,t2.dep_place_name as depPlaceName\n" +
            ",t2.arr_place_name as arrPlaceName,t1.ship_type_name as shipTypeName,t1.product_source product_source,t1.supplier_name as supplierName,t1.applicable_crowd_name as applicableCrowdName,t1.charge_user_id as chargeUserId,t1.charge_user_name chargeUserName ,\n" +
            "t1.sell_status sellStatus,t1.pack_status as packStatus,t1.approval_status as approvalStatus\n" +
            "from t_ship_product t1 \n" +
            "left join t_ship_route t2 on t1.route_id = t2.id \n" +
            "LEFT JOIN t_main_data_ship_cabinclass t3 on (t1.cabin_class = t3.type or t1.cabin_class_str = t3.type) "+
            "WHERE t1.is_deleted = 0 " +
//            "and t1.approval_status=5" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t1.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.code != null and req.code != ''\"> AND t1.code = #{req.code}</if> \n" +
            "<if test=\"req.voyage != null and req.voyage != ''\"> AND t1.voyage LIKE CONCAT('%',#{req.voyage},'%')</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t1.dep_city_name = #{req.depCityName}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t1.arr_city_name = #{req.arrCityName}</if> \n" +

            "<if test=\"req.depPlaceName != null and req.depPlaceId != ''\"> AND t2.dep_place_name like  CONCAT('%',#{req.depPlaceName },'%')</if> \n" +
            "<if test=\"req.arrPlaceName != null and req.arrPlaceId != ''\"> AND t2.arr_place_name like  CONCAT('%',#{req.arrPlaceName },'%')</if> \n" +

            "<if test=\"req.productSource != null and req.productSource != ''\"> AND t1.product_source = #{req.productSource}</if> \n" +

            "<if test=\"req.depTimeStart != null and req.depTimeEnd != null \"> AND substring(t1.dep_time,12,5) between #{req.depTimeStart} and #{req.depTimeEnd} </if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND (t1.cabin_class = #{req.cabinClass} or t1.cabin_class_str = #{req.cabinClass}) </if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> AND t1.supplier_name LIKE CONCAT('%',#{req.supplierName },'%')</if> \n" +
            "<if test=\"req.supplierName != null and req.supplierName != ''\"> AND t1.supplier_name LIKE CONCAT('%',#{req.supplierName },'%')</if> \n" +
            "<if test=\"req.productIds != null\"> AND t1.id in <foreach item='item' index='index' collection='req.productIds' open='(' separator=',' close=')'> #{item} </foreach></if> \n" +
            "order BY t1.gmt_create desc " + "</script>")
    List<ShipProductDTO> queryShipList(@Param("req") ShipProductPageReq shipProductPageReq);

    /**
     * 获取船票详情
     *
     * @param id
     * @return
     */
    @Select("<script>" + "select t1.id,t1.code,t1.dep_city_name as depCityName ,t1.arr_city_name as arrCityName,t1.company_type companyType,t1.voyage\n" +
            ",t1.cabin_class cabinClass,t1.dep_time as depTime,t1.arr_time as arrTime,t2.dep_place_name as depPlaceName\n" +
            ",t2.arr_place_name as arrPlaceName,t1.ship_type_name as shipTypeName,t1.product_source productSource,t1.supplier_name as supplierName,t1.supplier_id supplierId,t1.applicable_crowd_name as applicableCrowdName,t1.charge_user_id as chargeUserId,\n" +
            "t1.sell_status sellStatus,t1.pack_status as packStatus,t1.approval_status as approvalStatus ,\n" +
            "t6.sale_begin_date as saleBeginDate,t6.is_confirm as isConfirm,t6.booking_information as bookingInformation,t6.refund_content as refundContent,\n" +
            "t6.service_charge_price as serviceChargePrice,t6.refund_fee_price as refundFeePrice,t6.other_fee_price as otherFeePrice " +
            "from t_ship_product t1 \n" +
            "left join t_ship_route t2 on t1.route_id = t2.id \n" +
            "left join t_ship_product_reservation_settings t6 on t1.id = t6.ship_product_id \n" +
            "WHERE t1.is_deleted = 0 " +
            "<if test=\"ProductId != null and ProductId != ''\">AND t1.id = #{ProductId}</if> \n" +
            "</script>")
    ShipProductDetailDTO queryDetail(@Param("ProductId") Long id);

    /**
     * 获取船票的线下自营加幅集合 取最低价格加幅
     *
     * @param id
     * @return
     */
    @Select("<script>" + "select  ifnull(t1.amount,0) offLinePrice from \n" +
            "  t_ship_product_sale_strategy t\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t1 on t.id=t1.offline_id\n" +
            " where 1=1  \n" +
            "<if test=\"req != null and req != ''\">AND t.product_id = #{req}</if> \n" +
            "  order by ifnull(t1.amount,0) LIMIT 1" +
            "</script>")
    ShipRecommendListDTO selectShipOffLineAmountList(@Param("req") Long id);


    /**
     * 获取船票预订集合
     *
     * @param shipProductIsRecommendedReq
     * @return
     */
    @Select("<script>" + "SELECT * from (SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t1.route_name routeName,\n" +
            " t.ship_type_name shipTypeName,\n" +
            " t1.id routeId,\n" +
            " t3.daily_stock dailyStock,\n" +
            " t.company_type companyType,\n" +
            " t.supplier_name supplierName,\n" +
            " t.dep_city_id depCityId,\n" +
            " t5.refund_content refundContent,\n" +
            " t.arr_city_id arrCityId,\n" +
            " t1.dep_place_id depPlaceId,\n" +
            " t1.arr_place_id arrPlaceId,\n" +
            " t.dep_city_name depCityName,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " t.arr_city_name arrCityName,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t.cabin_class cabinClass,\n" +
            " t.cabin_class_name cabinClassName,\n" +
            " t.voyage voyage,\n" +
            " t10.adjust_type adjustType,\n" +
            " t10.amount amount,\n" +
            " t10.percent percent, " +
            " t10.limit_num - IFNULL(sum( t6.sold_number ),0) soldNumber,\n" +
            " (CASE " +
            " t10.adjust_type \n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t4.cost_price,0) + IFNULL(t10.amount,0) \n" +
            " WHEN '2' THEN\n" +
            " IFNULL(t4.cost_price,0) + ( IFNULL(t4.cost_price,0) * ( IFNULL(t10.percent,0) * 0.01 ) ) END ) orderPrice, " +
            " IFNULL( t4.cost_price, 0 ) cosPrice, " +
            " IFNULL( t5.service_charge_price, 0 ) servicePrice, " +
            " IFNULL( t5.other_fee_price, 0 )  taxPrice, " +
            " IFNULL( t4.cost_price, 0 ) + IFNULL( t5.service_charge_price, 0 ) + IFNULL( t5.other_fee_price, 0 ) AS price " +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 on t.id =t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.id = t10.offline_id \n" +
            " and t.id = t10.product_id\n" +
            " left JOIN t_ship_product_sale_record t6 on t.id =t6.product_id and t10.relation_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel " +
            " where t.is_deleted = 0 and t.is_recommend = 1 and t.shelves_status = 1 and t4.tourist_type_id = 1 and t9.visable_type in('2','3') and t9.channel_type =1 \n" +
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +
            "<if test=\"req.depTimeStart != null \"> " +
            " and t.dep_time &lt;= #{req.depTimeStart} and t.arr_time &gt;= #{req.arrTimeStart} </if> \n" +
            "<if test=\"req.id != null and req.id != ''\"> AND t.id = #{req.id}</if> \n" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.depPlaceId != null and req.depPlaceId != ''\"> AND t1.dep_place_id = #{req.depPlaceId}</if> \n" +
            "<if test=\"req.arrPlaceId != null and req.arrPlaceId != ''\"> AND t1.arr_place_id = #{req.arrPlaceId}</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t.dep_city_id = #{req.depCityId}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t.arr_city_id = #{req.arrCityId}</if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND t.cabin_class = #{req.cabinClass}</if> \n" +
            "<if test=\"userId != null \"> " +
            " and t10.relation_id in " +
            "<foreach collection='userId' item='id' open='(' separator=',' close=')'>"+
            "#{id}"+
            "</foreach>"+
            "</if> \n" +
            "and t.shelves_status=1"+
            " group by t.id " +
            "ORDER BY orderPrice ,t.is_recommend desc,t.gmt_create desc limit 15" +
            ")  t1" +
            "<if test=\"req.ticketNumber != null and req.ticketNumber != ''\"> where t1.soldNumber >= #{req.ticketNumber}</if></script>")
    List<ShipReserveListDTO> selectShipReserveList(@Param("req") ShipProductIsRecommendedReq shipProductIsRecommendedReq, @Param("userId") List<String> userIds );

    /**
     * 获取船票推荐集合
     * 查询上架切推荐的船票产品 tourist_type_id =1 是指只查询成人价最低价格的船票
     *
     * @param localDate
     * @return
     */
    @Select("<script>" + "SELECT * from (SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t.company_type companyType,\n" +
            " t1.route_name routeName,\n" +
            " t9.visable_type visableType,\n" +
            "  t10.relation_id relationId,\n" +
            " t.dep_city_name depCityName,\n" +
            " IFNULL(t2.exchange_rate,0) exchangeRate,\n" +
            " t.arr_city_name arrCityName,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " IFNULL( t5.other_fee_price, 0 ) otherFeePrice,\n" +
            " IFNULL( t5.service_charge_price, 0 ) serviceChargePrice,\n" +
            " t10.adjust_type adjustType,\n" +
            " t10.amount amount,\n" +
            " t10.percent percent, " +
            " (\n" +
            " case t10.is_limit when '1' then +\n" +
            " (case when IFNULL(t2.daily_stock,0) &gt;= IFNULL(t10.limit_num,0) then (IFNULL(t10.limit_num,0) - IFNULL(sum(t6.sold_number),0))\n" +
            " when IFNULL(t2.daily_stock,0) &lt;= IFNULL(t10.limit_num,0) then (IFNULL(t2.daily_stock,0) - IFNULL(sum(t6.sold_number),0))\n" +
            " end\n" +
            " )\n" +
            " when '0' then IFNULL(t2.daily_stock,0) - IFNULL(sum(t6.sold_number),0)\n" +
            " end\n" +
            " ) soldNumber,"+
            " (CASE " +
            " t10.adjust_type \n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t10.amount,0) \n" +
            " WHEN '2' THEN\n" +
            " IFNULL(t4.cost_price,0) * ( IFNULL(t10.percent,0) * 0.01 ) END ) taxFee, " +
            " IFNULL(t4.cost_price,0) costPrice " +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 on t.id =t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.id = t10.offline_id \n" +
            " AND t.id = t10.product_id\n" +
            " left JOIN t_ship_product_sale_record t6 on t.id =t6.product_id and t10.relation_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel " +
            " where t.is_deleted = 0 and t.is_recommend = 1 and t.shelves_status = 1 and t4.tourist_type_id = 1 and t9.visable_type in('2','3') and t9.channel_type =1   " +
            "<if test=\"req != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req} " +
            "</if> \n" +
            "<if test=\"userId != null \"> " +
            " and t10.relation_id in " +
            "<foreach collection='userId' item='id' open='(' separator=',' close=')'>"+
            "#{id}"+
            "</foreach>"+
            "</if> \n" +
            "and t.shelves_status=1"+
            " group by t.id " +
            " order by t.gmt_create desc limit 15" +
            ") t1 where t1.soldNumber >=1" +
            "</script>")
    List<ShipRecommendListDTO> selectShipRecommendList(@Param("req") LocalDate localDate, @Param("userId") List<String> userIds);

    /**
     * 获取船票线下自营价格集合
     *
     * @param id
     * @return
     */
    @Select("<script>" + " SELECT DISTINCT\n" +
            " t2.id offLineId,\n" +
            " t2.check_type checkType,\n" +
            " t2.NAME NAME,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount offLinePrice,\n" +
            " t2.percent percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " count( t3.sold_number ) soldNumber \n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t1 ON t.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t2 ON t.product_id = t2.product_id\n" +
            " LEFT JOIN t_ship_product_sale_record t3 ON t3.product_id = t.product_id \n" +
            "WHERE\n" +
            " 1 = 1 \n" +
            "<if test=\"req != null and req != ''\">AND t.product_id = #{req}</if> \n" +
            "<if test=\"localDate != null \"> " +
            " and DATE_FORMAT( t1.time_begin, '%Y-%m-%d' ) &lt;= #{localDate} AND DATE_FORMAT( t1.time_end, '%Y-%m-%d' ) &gt;= #{localDate} </if> \n" +
            " ORDER BY\n" +
            " ifnull( t2.amount, 0 ) " +
            "</script>")
    List<ShipReserveDTO> selectOfflineSelfList(@Param("req") Long id, @Param("localDate") LocalDate date);


    /**
     * 获取船票线下自营价格集合
     *
     * @param id
     * @return
     */
    @Select("<script>" + " SELECT DISTINCT\n" +
            " t2.id offLineId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount offLinePrice,\n" +
            " t2.percent percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " count( t3.sold_number ) soldNumber \n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t1 ON t.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_online_self_platform t2 ON t.product_id = t2.product_id\n" +
            " LEFT JOIN t_ship_product_sale_record t3 ON t3.product_id = t.product_id \n" +
            "WHERE\n" +
            " 1 = 1 \n" +
            "<if test=\"req != null and req != ''\">AND t.product_id = #{req}</if> \n" +
            "<if test=\"localDate != null \"> " +
            " and DATE_FORMAT( t1.time_begin, '%Y-%m-%d' ) &lt;= #{localDate} AND DATE_FORMAT( t1.time_end, '%Y-%m-%d' ) &gt;= #{localDate} </if> \n" +
            " ORDER BY\n" +
            " ifnull( t2.amount, 0 ) " +
            "</script>")
    List<ShipReserveDTO> selectOfflineOfficeSelfList(@Param("req") Long id, @Param("localDate") LocalDate date);
    /**
     * 获取船票预订集合
     *
     * @param shipProductManualReq
     * @return
     */
    @Select("<script>" + "SELECT * from ( " +
            "SELECT " +
            " t.id, " +
            " t.CODE, " +
            " t1.route_name routeName, " +
            " t1.id routeId, " +
            " t3.daily_stock dailyStock, " +
            " t.company_type companyType, " +
            " t.dep_city_id depCityId, " +
            " t.arr_city_id arrCityId, " +
            " t1.dep_place_id depPlaceId, " +
            " t1.arr_place_id arrPlaceId, " +
            " t.dep_city_name depCityName, " +
            " t.arr_city_name arrCityName, " +
            " t1.dep_place_name depPlaceName, " +
            " t1.arr_place_name arrPlaceName, " +
            " t.cabin_class cabinClass, " +
            " t.voyage voyage, " +
            " t10.adjust_type adjustType, " +
            " t10.amount amount, " +
            " t10.percent percent,  " +
            " t10.limit_num-IFNULL(sum( t6.sold_number ),0) soldNumber" +

            " FROM " +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 on t.id =t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.strategy_id = t10.offline_id \n" +
            " AND t9.visable_type = t10.check_type and t.id = t10.product_id\n" +
            " left JOIN t_ship_product_sale_record t6 on t.id =t6.product_id and t10.relation_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel " +
            " where t.is_deleted = 0 and t.is_recommend = 1 and t.shelves_status = 1 and t4.tourist_type_id = 1 \n" +
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +
            "<if test=\"req.depTimeStart != null \"> " +
            " and t.dep_time &lt;= #{req.depTimeStart} and t.arr_time &gt;= #{req.arrTimeStart} </if> \n" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t.dep_city_id = #{req.depCityId}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t.arr_city_id = #{req.arrCityId}</if> \n" +
            "<if test=\"req.routeId != null and req.routeId != ''\"> AND t1.id = #{req.routeId}</if> \n" +
            "<if test=\"req.voyage != null and req.voyage != ''\"> AND t.voyage = #{req.voyage}</if> \n" +
            " group by t.id " +
            "ORDER BY orderPrice ,t.is_recommend desc,t.gmt_create desc limit 15" +
            ")  t1 where t1.soldNumber >= #{req.ticketNumber} </script>")
    List<ShipReserveListDTO> selectShipManual(@Param("req") ShipProductManualReq shipProductManualReq);

    /**
     * 获取船票预订集合
     *
     * @param shipZJProductIsRecommendedReq
     * @return
     */
    @Select("<script>" + " select * from ( SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t1.route_name routeName,\n" +
            " t1.dep_place_code depPlaceCode,\n" +
            " t1.arr_place_code arrPlaceCode,\n" +
            " t1.id routeId,\n" +
            " t.stock dailyStock,\n" +
            " t5.refund_content refundContent,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " DATE_FORMAT(t.dep_time,'%Y-%m-%d') startDate,\n" +
            " t.company_type companyType,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t6.seat_rankt_name seatRanktName,\n" +
            " t.supplier_name supplierName,\n" +
            " t.cabin_class_str cabinClassStr,\n" +
            " t4.cost_price cosPrice,\n" +
            " t4.cost_code costCode,\n" +
            " t.voyage voyage\n" +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_seat t6 on t.cabin_class_str = t6.seat_rank_id\n" +
            " WHERE\n" +
            " t.is_deleted = 0 \n" +
            " AND t.shelves_status = 1 \n" +
            " AND t4.tourist_type_id = 1 \n" +
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.depPlaceCode != null and req.depPlaceCode != ''\"> AND t1.dep_place_code = #{req.depPlaceCode}</if> \n" +
            "<if test=\"req.arrPlaceCode != null and req.arrPlaceCode != ''\"> AND t1.arr_place_code = #{req.arrPlaceCode}</if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND t.cabin_class_str = #{req.cabinClass}</if> \n" +
            "<if test=\"req.productId != null and req.productId != ''\"> AND t.id = #{req.productId}</if> \n" +

            " GROUP BY\n" +
            " t.id,t.route_id,t.cabin_class_str\n" +
            " ORDER BY\n" +
            " depTime,t.is_recommend \n" +
            "   limit 15" +
            ")  t1 where t1.dailyStock >= #{req.ticketNumber} </script>")
    List<ShipReserveListDTO> selectApiList(@Param("req") ShipZJProductIsRecommendedReq shipZJProductIsRecommendedReq);


    /**
     * 预订中心-条件查询产品列表
     * @param shipProductSearchReq
     * @param userIds
     * @return
     */
    @Select("<script>"+"SELECT * from (SELECT\n" +
            " t1.id,\n" +
            " t1.code,\n" +
            " t1.routeName,\n" +
            " t1.shipTypeName,\n" +
            " t1.touristId,\n" +
            " t1.routeId,\n" +
            " t1.companyType,\n" +
            " t1.depTime,\n" +
            " t1.supplierId,\n" +
            " t1.supplierName,\n" +
            " t1.currencyId,\n" +
            " t1.currencyName,\n" +
            " t1.exchangeRate,\n" +
            " t1.depCityId,\n" +
            " t1.arrCityId,\n" +
            " t1.depPlaceId,\n" +
            " t1.arrPlaceId,\n" +
            " t1.depCityName,\n" +
            " t1.arrCityName,\n" +
            " t1.depPlaceName,\n" +
            " t1.arrPlaceName,\n" +
            " t1.cabinClass,\n" +
            " t1.isConfirm,\n" +
            " t1.cabinClassStr,\n" +
            " t1.voyage,\n" +
            " t1.adjustType,\n" +
            " t1.amount,\n" +
            " t1.visableType,\n" +
            " t1.relationId,\n" +
            " t1.percent,\n" +
            " t1.dailyStock as totalNumber,\n" +
            " t1.limitNum,\n" +
            " t1.isLimit,\n" +
            " case when t1.dailyStock > t1.limitNum then t1.limitNum - t1.soldNumber \n" +
            " else t1.dailyStock - t1.soldNumber end surplusNumber,\n" +
            " t1.touristTypeId,  \n" +
            " t1.orderPrice,\n" +
            " t1.cosPrice,\n" +
            " t1.servicePrice,\n" +
            " t1.taxPrice,\n" +
            " t1.price,\n" +
            " t1.applicableCrowdName,\n" +
            " t1.touristName\n" +
            " FROM\n" +
            " (\n" +
            " SELECT\n" +
            " t.id,\n" +
            " t. CODE,\n" +
            " t1.route_name routeName,\n" +
            " t.ship_type_name shipTypeName,\n" +
            " t1.id routeId,\n" +
            " t5.is_confirm isConfirm,\n" +
            " CASE\n" +
            " WHEN t2.daily_stock IS NULL THEN\n" +
            " 0\n" +
            " ELSE\n" +
            " t2.daily_stock\n" +
            " END dailyStock,\n" +
            " t.company_type companyType,\n" +
            " t.dep_city_id depCityId,\n" +
            " t.applicable_crowd_name applicableCrowdName,\n" +
            " t4.tourist_type_name touristName,\n" +
            " t.supplier_id supplierId,\n" +
            " t.supplier_name supplierName,\n" +
            " t.arr_city_id arrCityId,\n" +
            " t1.dep_place_id depPlaceId,\n" +
            " t1.arr_place_id arrPlaceId,\n" +
            " t.dep_city_name depCityName,\n" +
            " t.arr_city_name arrCityName,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t2.currency_id currencyId,\n" +
            " t2.currency_name currencyName,\n" +
            " IFNULL(t2.exchange_rate ,0) exchangeRate,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " t.cabin_class cabinClass,\n" +
            " t.cabin_class_name cabinClassStr,\n" +
            " t.voyage voyage,\n" +
            " t4.id touristId,\n" +
            " t10.adjust_type adjustType,\n" +
            " t10.amount amount,\n" +
            " t10.percent percent,\n" +
            "    IFNULL(sum(t6.sold_number), 0) soldNumber,\n" +
            " t10.limit_num limitNum,\n" +
            " t10.is_limit isLimit,\n" +
            " t9.visable_type visableType,\n" +
            " t10.relation_id relationId,\n" +
            " t4.tourist_type_id touristTypeId,\n" +
            " (\n" +
            " CASE t10.adjust_type\n" +
            " WHEN '1' THEN\n" +
            "  IFNULL(t10.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            "  IFNULL(t4.cost_price, 0) * (IFNULL(t10.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) orderPrice,\n" +
            " IFNULL(min(t4.cost_price), 0) cosPrice,\n" +

            " (\n" +
            " CASE t5.service_charge_type\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.service_charge_price, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(t4.cost_price, 0) * (IFNULL(t5.service_charge_price, 0) * 0.01)\n" +
            " END\n" +
            " ) servicePrice,\n" +
            " IFNULL(t5.other_fee_price, 0) taxPrice,\n" +
            " IFNULL(min(t4.cost_price), 0) + IFNULL(t5.service_charge_price, 0) + IFNULL(t5.other_fee_price, 0) AS price\n" +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 ON t.id = t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.id = t10.offline_id\n" +
            " AND t.id = t10.product_id\n" +
            " LEFT JOIN t_ship_product_sale_record t6 ON t.id = t6.product_id\n" +
            " AND t10.relation_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel\n" +
            " WHERE\n" +
            " t.is_deleted = 0\n" +

            //1.上架的产品
            " AND t.shelves_status = 1 \n" +

            //2.非禁销的产品
            " AND t9.visable_type IN ('2', '3')\n" +

            //3.自营产品 TODO 待定：代理商的产品是否能进来
            " AND t9.channel_type = 1\n" +

            //3.时效范围内的产品
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +

            "<if test=\"userIds != null \"> " +
            " AND t10.relation_id IN \n" +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>"+
            "#{id}"+
            "</foreach>"+
            "</if> \n" +
            //4.出发地、目的地；出发码头，目的地码头
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t.dep_city_id = #{req.depCityId}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t.arr_city_id = #{req.arrCityId}</if> \n" +
            "<if test=\"req.depPlaceId != null and req.depPlaceId != ''\"> AND t1.dep_place_id = #{req.depPlaceId}</if> \n" +
            "<if test=\"req.arrPlaceId != null and req.arrPlaceId != ''\"> AND t1.arr_place_id = #{req.arrPlaceId}</if> \n" +
            "<if test=\"req.id != null and req.id != ''\"> AND t.id = #{req.id}</if> \n" +

            //5.船舱等级
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND t.cabin_class = #{req.cabinClass}</if> \n" +

            " GROUP BY\n" +
            " t.id,\n" +
            " t.supplier_id,\n" +
            " t.cabin_class_name,\n" +
            " t4.tourist_type_id\n" +
            " ORDER BY\n" +
            " orderPrice,\n" +
            " t.is_recommend DESC,\n" +
            " t.gmt_create DESC\n" +
            " limit 15" +
            " ) t1\n" +
            " ) t2\n" +
            " WHERE 1=1 " +

            //6.非API产品
            " AND t2.companyType = 1" +

            "<if test=\"req.ticketNumber != null and req.ticketNumber !=0\"> " +
            " AND t2.surplusNumber >= #{req.ticketNumber}" +
            "</if> \n" +
            "</script>")
    List<ShipReserveListDTO> selectShipReserveNewList(@Param("req") ShipProductSearchReq shipProductSearchReq, @Param("userIds") List<String> userIds);

    /**
     * 查询珠江预订产品列表
     * @param shipProductSearchReq
     * @return
     */
    @Select("<script>" + " select * from ( SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t1.route_name routeName,\n" +
            " t5.is_confirm isConfirm,\n" +
            " t1.dep_place_code depPlaceCode,\n" +
            " t1.arr_place_code arrPlaceCode,\n" +
            " t.ship_type_name shipTypeName,\n" +
            " t1.id routeId,\n" +
            " t.stock surplusNumber,\n" +
            " t.stock totalNumber,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " DATE_FORMAT(t.dep_time,'%Y-%m-%d') startDate,\n" +
            " t.company_type companyType,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t6.seat_rankt_name seatRanktName,\n" +
            " t.cabin_class_str cabinClassStr,\n" +
            " t.supplier_id supplierId,\n" +
            " t.supplier_name supplierName,\n" +
            " t2.currency_id currencyId,\n" +
            " t2.currency_name currencyName,\n" +
            " t4.cost_price cosPrice,\n" +
            " t4.cost_code costCode,\n" +
            " t4.id touristId,\n" +
            " t4.tourist_type_id touristTypeId,\n" +
            " t.voyage voyage\n" +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_seat t6 on t.cabin_class_str = t6.seat_rank_id\n" +
            " WHERE\n" +
            " t.is_deleted = 0 \n" +
            " AND t.shelves_status = 1 \n" +
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.depPlaceCode != null and req.depPlaceCode != ''\"> AND t1.dep_place_code = #{req.depPlaceCode}</if> \n" +
            "<if test=\"req.arrPlaceCode != null and req.arrPlaceCode != ''\"> AND t1.arr_place_code = #{req.arrPlaceCode}</if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND t.cabin_class_str = #{req.cabinClass}</if> \n" +
            "<if test=\"req.id != null and req.id != ''\"> AND t.id = #{req.id}</if> \n" +

            " GROUP BY\n" +
            " t.id,t.route_id,t.cabin_class_str,t4.tourist_type_id\n" +
            " ORDER BY\n" +
            " depTime,t.is_recommend \n" +
            "   limit 15" +
            ")  t1 where t1.surplusNumber >= #{req.ticketNumber} </script>")
    List<ShipReserveListDTO> selectNewApiList(@Param("req") ShipProductSearchReq shipProductSearchReq);

    @Select("<script>"+"" +
            " SELECT\n" +
            " (\n" +
            " CASE t5.adjust_type\n" +
            " WHEN 1 THEN\n" +
            " t5.amount\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(t5.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) agentFee,\n" +
            " (\n" +
            " CASE s.service_charge_type\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(s.service_charge_price, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(s.service_charge_price, 0) * 0.01)\n" +
            " END\n" +
            " ) serviceChargePrice,\n" +
            " IFNULL(y2.cost_price, 0) price," +
            " y1.ship_product_id productId,\n" +
            " t4.visable_type,\n" +
            " t5.agent_id agentId,\n" +
            " t5.agent_name agentName,\n" +
            " s.other_fee_price taxFee,\n" +
            "  y1.currency_id currencyId\n" +
            " FROM\n" +
            " t_ship_product t1\n" +
            " LEFT JOIN t_ship_product_reservation_settings s on t1.id = s.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost y1 ON t1.id = y1.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t4 ON t1.id = t4.product_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_agent_custom t5 ON t4.id = t5.offline_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.date_begin, '%Y-%m-%d') &lt;= #{req.depQueryDate1}\n" +
            " AND DATE_FORMAT(y3.date_end, '%Y-%m-%d') &gt;= #{req.depQueryDate1}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{req.depQueryDate1}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{req.depQueryDate1}\n" +
            " AND t1.id =#{req.productId1} \n" +
            " <if test='req.agentName != null'>" +
            " AND t5.agent_name like concat(concat('%', #{req.agentName}), '%')"+
            " </if>" +
            " GROUP BY\n" +
            " t1.id" +

            " union all" +

            " SELECT\n" +
            " (\n" +
            " CASE t5.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t5.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(t5.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) agentFee,\n" +
            " (\n" +
            " CASE s.service_charge_type\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(s.service_charge_price, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(y2.cost_price, 0) * (IFNULL(s.service_charge_price, 0) * 0.01)\n" +
            " END\n" +
            " ) serviceChargePrice,\n" +
            " IFNULL(y2.cost_price, 0) price," +
            " y1.ship_product_id productId,\n" +
            " t4.visable_type,\n" +
            " t5.agent_id agentId,\n" +
            " t5.agent_name agentName,\n" +
            " s.other_fee_price taxFee,\n" +
            " y1.currency_id currencyId\n" +
            " FROM\n" +
            " t_ship_product t1\n" +
            " LEFT JOIN t_ship_product_reservation_settings s on t1.id = s.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost y1 ON t1.id = y1.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t4 ON t1.id = t4.product_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_agent_custom t5 ON t4.id = t5.offline_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.date_begin, '%Y-%m-%d') &lt;= #{req.arrQueryDate2}\n" +
            " AND DATE_FORMAT(y3.date_end, '%Y-%m-%d') &gt;= #{req.arrQueryDate2}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{req.arrQueryDate2}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{req.arrQueryDate2}\n" +
            " AND t1.id =#{req.productId2} \n" +
            " <if test='req.agentName != null'>" +
            " AND t5.agent_name like concat(concat('%', #{req.agentName}), '%')"+
            " </if>" +
            " GROUP BY\n" +
            " t1.id" +
            ""+"</script>")
    IPage<AgentDto> queryAgentList(@Param("req") AgentReq req, IPage<ShipProduct> iPage);

    @Select("<script>SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t1.route_name routeName,\n" +
            " t.dep_city_id depCityId,\n" +
            " t.dep_city_name depCityName,\n" +
            " t.arr_city_id arrCityId,\n" +
            " t.arr_city_name arrCityName,\n" +
            " t1.dep_place_id depPlaceId,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_id arrPlaceId,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t2.currency_id currencyId,\n" +
            " t2.currency_name currencyName,\n" +
            " IFNULL(t2.exchange_rate, 0) exchangeRate,\n" +
            " t4.tourist_type_id touristTypeId,\n" +
            " t4.tourist_type_name touristTypeName,\n" +
            " IFNULL(t5.other_fee_price, 0) AS taxFee,\n" +
            " IFNULL(t5.service_charge_price, 0) charge,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " t.cabin_class_name cabinClassName,\n" +
            " t.cabin_class cabinClass,\n" +
            " t.voyage voyage,\n" +
            " t10.adjust_type adjustType,\n" +
            " t10.amount amount,\n" +
            " t10.percent percent,\n" +
            " t10.limit_num - IFNULL(sum(t6.sold_number), 0) soldNumber,\n" +
            " (\n" +
            " CASE t10.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t10.amount, 0)\n" +
            " WHEN '2' THEN\n" +
            " IFNULL(t4.cost_price, 0) * (IFNULL(t10.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) addFee,\n" +
            " IFNULL(t4.cost_price, 0) costPrice\n" +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 ON t.id = t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t10 ON t9.id = t10.offline_id\n" +
            " AND t.id = t10.product_id\n" +
            " LEFT JOIN t_ship_product_sale_record t6 ON t.id = t6.product_id\n" +
            " AND t10.relation_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel\n" +
            " WHERE\n" +
            " t.is_deleted = 0\n" +
            " AND t.is_recommend = 1\n" +
            " AND t.shelves_status = 1\n" +
            " AND t9.visable_type IN ('2', '3')\n" +
            " AND t9.channel_type = 1\n" +
            " AND DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req.queryDate}\n" +
            " AND DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req.queryDate}\n" +
            " AND t.id = #{req.productId}\n" +
            " AND t.shelves_status = 1\n" +
            " GROUP BY\n" +
            " t.id,\n" +
            " t4.tourist_type_id\n" +
            " ORDER BY\n" +
            " t.gmt_create DESC</script>\n")
    List<ShipBaseInfoDTO> queryShipPriceByDate(@Param("req") ShipPriceReq shipPriceReq);

    @Select("select dep_city_name depCityName,arr_city_name arrCityName from t_ship_route where dep_place_name=#{depPlaceName} and arr_place_name=#{arrPlaceName} limit 1")
    ReserveCityDto queryReserveCity(@Param("depPlaceName") String depPlaceName, @Param("arrPlaceName") String arrPlaceName);

    @Select("select dep_city_name depCityName,arr_city_name arrCityName from t_ship_route where dep_place_id=#{depPlaceId} and arr_place_id=#{arrPlaceId} limit 1")
    ReserveCityDto queryReserveIdCity(@Param("depPlaceId") Long depPlaceId, @Param("arrPlaceId") Long arrPlaceId);

    /**
     * 查询成本ID，用于扣减库存
     * @param productId
     * @param depTime
     * @return
     */
    @Select("<script> select t1.id from " +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_product_cost t1 ON t.id = t1.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t2 ON t1.id = t2.cost_id\n" +
            " WHERE 1=1 AND DATE_FORMAT(t2.date_begin, '%Y-%m-%d') &lt;= #{queryDate}\n" +
            " AND DATE_FORMAT(t2.date_end, '%Y-%m-%d') &gt;= #{queryDate}\n" +
            " AND t.id=#{productId} </script>"
    )
    Long selectActuallyCostForRoute(@Param("productId") Long productId, @Param("queryDate") LocalDate depTime);

    @Select("<script>SELECT\n" +
            " t0.product_id productId,\n" +
            " t1.visable_type visableType,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.relation_id relationId,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t4.daily_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t0\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t1 ON t0.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_self_custom t2 ON t2.offline_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t4 ON t0.product_id = t4.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " and t2.offline_id is not null \n" +
            " GROUP BY\n" +
            " t0.product_id</script>")
    List<ShipAddPriceDTO> queryOffLineSalePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate);

    @Select("<script>SELECT\n" +
            " t0.product_id productId,\n" +
            " t1.visable_type visableType,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.agent_id relationId,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t4.daily_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t0\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t1 ON t0.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline_agent_custom t2 ON t2.offline_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t4 ON t0.product_id = t4.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " and t2.offline_id is not null \n" +
            " GROUP BY\n" +
            " t0.product_id</script>")
    List<ShipAddPriceDTO> queryAgentSalePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate);

    @Select("<script>SELECT\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t4.daily_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t0\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t1 ON t0.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_online_self_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_ship_product_cost t4 ON t0.product_id = t4.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " AND t2.channel_name is not null\n" +
            " AND t2.channel_id=#{relId}\n" +
            " GROUP BY\n" +
            " t0.product_id</script>")
    List<ShipAddPriceDTO> querySaleOfficePrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);

    @Select("<script>SELECT\n" +
            " t0.product_id productId,\n" +
            " t2.adjust_type adjustType,\n" +
            " t2.amount,\n" +
            " t2.percent,\n" +
            " t2.is_limit isLimit,\n" +
            " t2.limit_num limitNum,\n" +
            " (\n" +
            " CASE t2.adjust_type\n" +
            " WHEN '1' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + t2.amount\n" +
            " WHEN '2' THEN\n" +
            " (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) + (\n" +
            " IFNULL(t5.cost_price, 0) * IFNULL(t4.exchange_rate, 0)\n" +
            " ) * (IFNULL(t2.percent, 0) * 0.01)\n" +
            " END\n" +
            " ) costAndFee,\n" +
            " t5.cost_price costPrice,\n" +
            " t4.daily_stock solidStock,\n" +
            " t4.exchange_rate exchangeRate\n" +
            " FROM\n" +
            " t_ship_product_sale_strategy t0\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t3 ON t0.id = t3.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t1 ON t0.id = t1.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_online_third_platform t2 ON t2.strategy_id = t0.id\n" +
            " LEFT JOIN t_ship_product_cost t4 ON t0.product_id = t4.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t5 ON t4.id = t5.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_date t6 ON t4.id = t6.cost_id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_begin, '%Y-%m-%d') &lt;= #{departureDate}\n" +
            " AND DATE_FORMAT(t6.date_end, '%Y-%m-%d') &gt;= #{departureDate}\n" +
            " AND t0.product_id = #{productId}\n" +
            " AND t2.platform_name is not null\n" +
            " AND t2.platform_id=#{relId}\n" +
            " GROUP BY\n" +
            " t0.product_id</script>")
    List<ShipAddPriceDTO> querySaleThirdPrice(@Param("productId") Long productId, @Param("departureDate") LocalDate departureDate, @Param("relId") Long relId);

    @Select("<script>" + "SELECT * from (SELECT\n" +
            " t.id,\n" +
            " t.CODE,\n" +
            " t1.route_name routeName,\n" +
            " t.ship_type_name shipTypeName,\n" +
            " t1.id routeId,\n" +
            " t3.daily_stock dailyStock,\n" +
            " t.company_type companyType,\n" +
            " t.supplier_name supplierName,\n" +
            " t.dep_city_id depCityId,\n" +
            " t5.refund_content refundContent,\n" +
            " t.arr_city_id arrCityId,\n" +
            " t1.dep_place_id depPlaceId,\n" +
            " t1.arr_place_id arrPlaceId,\n" +
            " t.dep_city_name depCityName,\n" +
            " substring(t.dep_time,12,5) depTime,\n" +
            " t.arr_city_name arrCityName,\n" +
            " t1.dep_place_name depPlaceName,\n" +
            " t1.arr_place_name arrPlaceName,\n" +
            " t.cabin_class cabinClass,\n" +
            " t.cabin_class_name cabinClassName,\n" +
            " t.voyage voyage,\n" +
            " t10.adjust_type adjustType,\n" +
            " t10.amount amount,\n" +
            " t10.percent percent, " +
            " t10.limit_num - IFNULL(sum( t6.sold_number ),0) soldNumber,\n" +
            " (CASE " +
            " t10.adjust_type \n" +
            " WHEN '1' THEN\n" +
            " IFNULL(t4.cost_price,0) + IFNULL(t10.amount,0) \n" +
            " WHEN '2' THEN\n" +
            " IFNULL(t4.cost_price,0) + ( IFNULL(t4.cost_price,0) * ( IFNULL(t10.percent,0) * 0.01 ) ) END ) orderPrice, " +
            " IFNULL( t4.cost_price, 0 ) cosPrice, " +
            " IFNULL( t5.service_charge_price, 0 ) servicePrice, " +
            " IFNULL( t5.other_fee_price, 0 )  taxPrice, " +
            " IFNULL( t4.cost_price, 0 ) + IFNULL( t5.service_charge_price, 0 ) + IFNULL( t5.other_fee_price, 0 ) AS price " +
            " FROM\n" +
            " t_ship_product t\n" +
            " LEFT JOIN t_ship_route t1 ON t.route_id = t1.id\n" +
            " LEFT JOIN t_ship_product_cost t2 ON t.id = t2.ship_product_id\n" +
            " LEFT JOIN t_ship_product_cost_date t3 ON t2.id = t3.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t4 ON t2.id = t4.cost_id\n" +
            " LEFT JOIN t_ship_product_reservation_settings t5 ON t.id = t5.ship_product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy t7 on t.id =t7.product_id\n" +
            " LEFT JOIN t_ship_product_sale_strategy_vaild_date t8 ON t7.id = t8.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_offline t9 ON t8.strategy_id = t9.strategy_id\n" +
            " LEFT JOIN t_ship_product_channel_online_self_platform t10 ON t7.id = t10.strategy_id \n" +
            " and t.id = t10.product_id\n" +
            " left JOIN t_ship_product_sale_record t6 on t.id =t6.product_id and t10.channel_id = t6.custom_id\n" +
            " AND t9.channel_type = t6.channel " +
            " where t.is_deleted = 0 and t.is_recommend = 1 and t.shelves_status = 1 and t4.tourist_type_id = 1 and t9.visable_type in('2','3') and t9.channel_type =1 \n" +
            "<if test=\"req.startDate != null \"> " +
            " and DATE_FORMAT(t3.date_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t3.date_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            " and DATE_FORMAT(t8.time_begin, '%Y-%m-%d') &lt;= #{req.startDate} and DATE_FORMAT(t8.time_end, '%Y-%m-%d') &gt;= #{req.startDate} " +
            "</if> \n" +
            "<if test=\"req.depTimeStart != null \"> " +
            " and t.dep_time &lt;= #{req.depTimeStart} and t.arr_time &gt;= #{req.arrTimeStart} </if> \n" +
            "<if test=\"req.id != null and req.id != ''\"> AND t.id = #{req.id}</if> \n" +
            "<if test=\"req.companyType != null and req.companyType != ''\"> AND t.company_type = #{req.companyType}</if> \n" +
            "<if test=\"req.depPlaceId != null and req.depPlaceId != ''\"> AND t1.dep_place_id = #{req.depPlaceId}</if> \n" +
            "<if test=\"req.arrPlaceId != null and req.arrPlaceId != ''\"> AND t1.arr_place_id = #{req.arrPlaceId}</if> \n" +
            "<if test=\"req.depCityId != null and req.depCityId != ''\"> AND t.dep_city_id = #{req.depCityId}</if> \n" +
            "<if test=\"req.arrCityId != null and req.arrCityId != ''\"> AND t.arr_city_id = #{req.arrCityId}</if> \n" +
            "<if test=\"req.cabinClass != null and req.cabinClass != ''\"> AND t.cabin_class = #{req.cabinClass}</if> \n" +
            " and t.shelves_status=1"+
            " and t10.channel_id=#{channelId}"+

            " group by t.id " +
            " ORDER BY t.gmt_create desc limit 15" +
            ")  t1" +
            "<if test=\"req.ticketNumber != null and req.ticketNumber != ''\"> where t1.soldNumber >= #{req.ticketNumber}</if></script>")
    List<ShipReserveListDTO> selectAppOfficeShipReserveList(@Param("req") AppShipProductIsRecommendedReq shipProductIsRecommendedReq, @Param("channelId") Long channelId);

    /**
     * 查询产品旅客类型的成本价
     * @param productId
     * @param queryDate
     * @return
     */
    @Select("<script>select t3.tourist_type_id touristTypeId,t3.tourist_type_name touristTypeName,t3.cost_price costPrice from t_ship_product_cost t1 " +
            " LEFT JOIN t_ship_product_cost_date t2 ON t1.id = t2.cost_id\n" +
            " LEFT JOIN t_ship_product_cost_tourist t3 ON t1.id = t3.cost_id\n" +
            " where t1.ship_product_id=#{productId}" +
            " and DATE_FORMAT(t2.date_begin, '%Y-%m-%d') &lt;= #{queryDate} and DATE_FORMAT(t2.date_end, '%Y-%m-%d') &gt;= #{queryDate} " +
            "group by t3.tourist_type_id</script>"
    )
    List<PeoplesPriceDto> getPeoplesPrices(@Param("productId") Long productId, @Param("queryDate") LocalDate queryDate);
}
